package training.osms.persistence.category;

import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.TypedQuery;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;

import org.apache.commons.lang.StringUtils;

import training.osms.model.category.Category;
import training.osms.model.category.CategorySearch;
import training.osms.model.category.Category_;
import training.osms.persistence.AbstractManagerTemplate;
import training.osms.persistence.EntityManagerFactorySingleton;


public class CategoryDAO{

	public void updateCategory(final Category category) {
		AbstractManagerTemplate manager = new AbstractManagerTemplate() {

			@Override
			protected void execute(EntityManager manager) {
				manager.merge(category);

			}
		};
		manager.execute();
	}


	public void insertCategory(final Category category) {
		AbstractManagerTemplate manager = new AbstractManagerTemplate() {

			@Override
			protected void execute(EntityManager manager) {
				manager.persist(category);

			}
		};
		manager.execute();
	}

	public Category findCategory(String name) {
		EntityManager manager = null;
		StringBuilder jpql = new StringBuilder("select cat from ");
		jpql.append(Category.class.getName());
		jpql.append(" cat where trim(lower(cat.name)) = :catName");
		try {
			manager = EntityManagerFactorySingleton.FACTORY.createEntityManager();
			TypedQuery<Category> query = manager.createQuery(jpql.toString(), Category.class);
			query.setParameter("catName", name.toLowerCase().trim());
			List<Category> result = query.getResultList();
			if (result.isEmpty()){
				return null;
			}else{
				return result.get(0);
			}
		}finally{
			try{
				if (manager != null){
					manager.close();
				}                               
			}catch(RuntimeException e){
				e.printStackTrace();
			}
		}
	}

	private Predicate toPredicate(CategorySearch options, CriteriaBuilder builder, Root<Category> root){
		Predicate namePredicate;

		String catName = options.getName();		


		String description = options.getDescription();

		if(!StringUtils.isBlank(catName)){
			namePredicate = builder.like(
					builder.lower(root.get(Category_.name)), 
					toLikeParameter(catName)
					);

		}else{
			namePredicate = builder.conjunction();
		}

		Predicate descriptionPredicate;

		if(!StringUtils.isBlank(description)){
			descriptionPredicate = builder.like(
					builder.lower(root.get(Category_.description)), 
					toLikeParameter(description)
					);

		}else{
			descriptionPredicate = builder.conjunction();
		}

		Predicate predicate = builder.and(namePredicate, descriptionPredicate);
		return predicate;
	}
	
	public Integer selectCategoryCount(CategorySearch options){
		EntityManager manager = null;


		try {
			manager = EntityManagerFactorySingleton.FACTORY.createEntityManager();
			CriteriaBuilder builder = manager.getCriteriaBuilder();

			//tipo de retorno			
			CriteriaQuery<Long> criteriaQuery = builder.createQuery(Long.class);
			//tabela de consulta
			Root<Category> root = criteriaQuery.from(Category.class);		



			criteriaQuery.select(builder.count(root));
			criteriaQuery.where(toPredicate(options, builder, root));


			TypedQuery<Long> query = manager.createQuery(criteriaQuery);

			Long result = query.getSingleResult();
			return result.intValue();

		}finally{
			try{
				if (manager != null){
					manager.close();
				}				
			}catch(RuntimeException e){
				e.printStackTrace();
			}
		}
	}



	public List<Category> searchCategories(CategorySearch search) {
		return searchCategoriesJpql(search);
	}

	public List<Category> searchCategoriesJpql(CategorySearch search) {
		EntityManager manager = null;
		StringBuilder jpql = new StringBuilder("select b from ");
		jpql.append(Category.class.getName());
		jpql.append(" b where 1 = 1 ");

		String blogName = search.getName();		
		if(blogName != null && blogName.trim().length() > 0){
			jpql.append(" and lower(b.name) like :catName");
		}

		String description = search.getDescription();
		if(description != null && description.trim().length() > 0){
			jpql.append(" and lower(b.description) like :catDescription");
		}

		jpql.append(" order by b.name ");

		try {
			manager = EntityManagerFactorySingleton.FACTORY.createEntityManager();
			TypedQuery<Category> query = manager.createQuery(jpql.toString(), Category.class);

			if (search.getFirstResult() != null){
				query.setFirstResult(search.getFirstResult());
			}
			if (search.getMaxResults() != null){
				query.setMaxResults(search.getMaxResults());
			}

			if(blogName != null && blogName.trim().length() > 0){
				query.setParameter("catName", toLikeParameter(blogName.toLowerCase().trim()));
			}

			if(description != null && description.trim().length() > 0){
				query.setParameter("catDescription", toLikeParameter(description.toLowerCase().trim()));
			}



			List<Category> result = query.getResultList();
			return result;

		}finally{
			try{
				if (manager != null){
					manager.close();
				}				
			}catch(RuntimeException e){
				e.printStackTrace();
			}
		}
	}

//	public List<Category> searchCategoriesCriteria(CategorySearch search) {
//		osmsDAO.startTransaction();
//
//		String catName = search.getName();		
//
//
//		String description = search.getDescription();
//
//
//		CriteriaBuilder builder = osmsDAO.getManager().getCriteriaBuilder();
//
//		//tipo de retorno			
//		CriteriaQuery<Category> criteriaQuery = builder.createQuery(Category.class);
//		//tabela de consulta
//		Root<Category> root = criteriaQuery.from(Category.class);
//
//		Predicate namePredicate;
//
//		if(!StringUtils.isBlank(catName)){
//			namePredicate = builder.like(
//					builder.lower(root.get(Category_.name)), 
//					toLikeParameter(catName)
//					);
//
//		}else{
//			namePredicate = builder.conjunction();
//		}
//
//		Predicate descriptionPredicate;
//
//		if(!StringUtils.isBlank(description)){
//			descriptionPredicate = builder.like(
//					builder.lower(root.get(Category_.description)), 
//					toLikeParameter(description)
//					);
//
//		}else{
//			descriptionPredicate = builder.conjunction();
//		}
//
//
//		Predicate predicate = builder.and(namePredicate, descriptionPredicate);
//		criteriaQuery.select(root);
//		criteriaQuery.where(predicate);
//		Order order = builder.asc(root.get(Category_.name));
//		criteriaQuery.orderBy(order);
//
//		TypedQuery<Category> query = osmsDAO.getManager().createQuery(criteriaQuery);
//
//		List<Category> result = query.getResultList();
//		osmsDAO.endTransaction();
//		return result;
//
//
//	}


	private String toLikeParameter(String trim) {
		return "%"+trim.toLowerCase().trim()+"%";
	}


	public void deleteCategory(final Category category) {
		AbstractManagerTemplate manager = new AbstractManagerTemplate() {

			@Override
			protected void execute(EntityManager manager) {
				Category databaseCategory = manager.find(Category.class, category.getIdentifier());
				manager.remove(databaseCategory);

			}
		};
		manager.execute();
	}


}
